/*
 * Skal tilføjes DBSupplier

 public Product findSupplier(int ID, boolean retriveAssociation)
    {
        String wClause = "  personID = '" + ID + "'";
        return singleWhere(wClause, retriveAssociation);
    }

 */






/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Tommy
 */
public class DBProduct implements IFDBProduct{

    private Connection con;

    public DBProduct()
    {
        con = DbConnection.getInstance().getDBcon();
    }

    // get all Product
    public ArrayList getAllProduct(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

    //get one Product having the ID
    public Product findProduct(int ID, boolean retriveAssociation)
    {
        String wClause = "  productID = '" + ID + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    //find one Product having the Product name
    public Product searchProductOnName(String name, boolean retriveAssociation)
    {
        String wClause = "name = '" + name + "'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    //insert new Product
    public int insert(Product prod)
    {  //call to get the next
        int nextProID = GetMax.getMaxId("Select max(productID) ");
        nextProID = nextProID + 1;
        System.out.println("next product ID = " + nextProID);

        int rc = -1;
        String query = "INSERT INTO Product(productID, etc, etc)  VALUES('"
                + nextProID + "','"
                + prod.getName() + "','"
                + prod.getPurchasePrice() + "','"
                + prod.getSalesPrice() + "','"
                + prod.getRentPrice() + "','"
                + prod.getCountryOfOrigin() + "','"
                + prod.getMinStock() + "','"
                + prod.getSupplier() + "',')";

        System.out.println("insert : " + query);
        try
        { // insert new employee
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }//end try
        catch (Exception ex)
        {
            System.out.println("Insert exception in product/prod db: " + ex);
        }
        return (rc);
    }

    //update Product
    public int update(Product prod)
    {
        Product prodObj = prod;
        int rc = -1;

        String query = "UPDATE Product SET "
                + "name ='" + prodObj.getName() + "' "
                + "purchasePrise ='" + prodObj.getPurchasePrice() + "' "
                + "salesPrice ='" + prodObj.getSalesPrice() + "' "
                + "rentPrice ='" + prodObj.getRentPrice() + "' "
                + "countryID ='" + prodObj.getCountryOfOrigin() + "' "
                + "minStock ='" + prodObj.getMinStock() + "' "
                + "supplierID ='" + prodObj.getSupplier() + "' "
                + " WHERE productID = '" + prodObj.getProductID() + "'";
        System.out.println("Update query:" + query);

        try
        { // update Department
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }//slut try
        catch (Exception ex)
        {
            System.out.println("Update exception in Department db: " + ex);
        }
        return (rc);
    }

    //delete Product
    public int delete(Product prod)
    {
        int rc = -1;

        String query = "DELETE FROM Product WHERE productID = '"
                + prod + "'";
        System.out.println(query);
        try
        { // delete from product
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }//slut try
        catch (Exception ex)
        {
            System.out.println("Delete exception in product/Product db: " + ex);
        }
        return (rc);
    }


    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Product P, Country C, Supplier S";
        if (wClause.length()>0)
            query=query+" WHERE "+ wClause;
        return query;
    }

    private Product buildProduct(ResultSet results)
    {
        Product prodObj = new Product();
        try
        {
            //Product
            prodObj.setProductID(results.getString(1));
            prodObj.setName(results.getString(2));
            prodObj.setPurchasePrice(results.getDouble(3));
            prodObj.setSalesPrice(results.getDouble(4));
            prodObj.setRentPrice(results.getDouble(5));
            prodObj.setCountryOfOrigin(results.getString(6));
            prodObj.setMinStock(results.getInt(7));
            prodObj.setSupplier(new Supplier(results.getString(8)));
        }
        catch (Exception e)
        {
            System.out.println("error in building the Product object");
        }
        return prodObj;
    }

    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Product> list = new ArrayList<Product>();
        String query = buildQuery(wClause);
        System.out.println("DBProduct" + query);
        try
        { // read from Product
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next())
            {
                Product prodObj = new Product();
                prodObj = buildProduct(results);
                list.add(prodObj);
            }//end while
            stmt.close();

            if (retrieveAssociation)// for each Product object find the supplier name
            {
                IFDBSupplier dbSupplier = new DbSupplier();
                for (Product prodObj : list)
                {
                    int supID = prodObj.getSupplier().getPersonID();
                    Supplier supObj = dbSupplier.findSupplier(supID, false);
                    prodObj.setSupplier(supObj);
                }
            }
        }//slut try
        catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }

    private Product singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Product prodObj = new Product();
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        { // read the Product from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                prodObj = buildProduct(results);
                //assocaition is to be build
                stmt.close();

                IFDBSupplier dbSupplier = new DbSupplier();
                if (retrieveAssociation)
                {   //The manager is to be build as well
                    int supID = prodObj.getSupplier().getPersonID();
                    Supplier supObj = dbSupplier.findSupplier(supID, false);
                    prodObj.setSupplier(supObj);
                    System.out.println("Supplier is seleceted");
                }
                else
                {
                    prodObj.setSupplier(null);
                }
            }
        }//end try
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return prodObj;
    }
}
